package dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import dao.AdressDao;
import dbpool.DbPool;
import entity.Adress;
import entity.User;

public class AdressDaoImpl implements AdressDao {

	QueryRunner runner = new QueryRunner();
	DbPool dbpool = DbPool.getPool();
	
	@Override
	public List<Adress> userAdress(int user_id) throws SQLException {
		String sql = "select * from adress where adress_id in (select adress_id from user2adress where user_id=?);";
		Connection conn = dbpool.getConn();
		List<Adress> adressList = (List) runner.query(conn, sql, new BeanListHandler(Adress.class),user_id);
		return adressList;
	}

	@Override
	public List<Adress> allAdress() throws SQLException {
		String sql = "select * from adress";
		Connection conn = dbpool.getConn();
		List<Adress> list = (List<Adress>) runner.query(conn, sql, new BeanListHandler(Adress.class));
		return list;
	}

	@Override
	public int delAdress(int user_id) throws SQLException {
		String sql = "delete from adress where adress_id in (select adress_id from user2adress where user_id=?)";
		String sql1 = "delete from user2adress where user_id=?";
		Connection conn = dbpool.getConn();
		int n = runner.update(conn, sql, user_id);
		int m = runner.update(conn, sql1, user_id);
		System.out.println("删除成功，影响两张表，影响行数分别是"+n+"--"+m+"行");
		return m;
	}

	@Override
	public int updataAdress(int user_id, String adress) throws SQLException {
		String sql = "update adress set adress_name=? where adress_id in (select adress_id from user2adress where user_id=?)";
		Connection conn = dbpool.getConn();
		int n = runner.update(conn, sql, adress,user_id);
		System.out.println("修改成功,影响两张表，影响"+n+"行");
		return n;
	}

	@Override
	public int addAdress(int user_id, String adress) throws SQLException {
		String sql = "insert into adress values(null,?)";
		String sql2 = "insert into user2adress values(null,?,?)";
		Connection conn = dbpool.getConn();
		User user = new UserDaoImpl().queryUserById(user_id);
		if(user!=null) {
			int n = runner.update(conn, sql, adress);
			int adress_id = queryByAdress(adress).getAdress_id();
			System.out.println(adress_id);
			if(adress_id!=0) {
				int m = runner.update(conn, sql2, user_id,adress_id);
				System.out.println("添加成功，影响两张表，各影响"+n+"--"+m+"行");
				return m;
			}
		}else {
			System.out.println("用户没找到");
			return 0;
		}
		return 0;
	}

	@Override
	public Adress queryByAdress(String adress) throws SQLException {
		String sql ="select * from adress where adress_name=?";
		Connection conn = dbpool.getConn();
		Adress adr = (Adress) runner.query(conn, sql, new BeanHandler(Adress.class),adress);
		dbpool.back(conn);
		return adr;
	}

	@Override
	public int delOneAdress(int adress_id) throws SQLException {
		
		String sql = "delete from user2adress where adress_id=?";
		String sql1 = "delete from adress where adress_id=?";
		Connection conn = dbpool.getConn();
		int n = runner.update(conn, sql, adress_id);
		int m = runner.update(conn, sql1, adress_id);
		System.out.println("删除成功，影响两张表，影响行数分别是"+n+"--"+m+"行");
		return m;
		
		
		
		
	}

}
